<?php
require_once '../db/db.php';

$db = openDbConn();

function mpnode( $val, $lft = array(), $rgt = array() ) {
  $tree = array( 'val' => $val );
  $tree['lftn'] = 1;
  $tree['rgtn'] = 2;
 
  if( $lft ) {
    ++$lft['lftn'];
    if( !$rgt ) {
      $tree['rgtn'] = $lft['rgtn'] + 1;
    } else {
      $tree['rgtn'] = $rgt['rgtn'] + 1;
    }
  }

  $tree['lft'] = $lft;
  $tree['rgt'] = $rgt;

  return $tree;  
}
echo '<pre>';
// array( '*' => array('post' =>  array('edit','delete'), 'forum' => array('edit', 'delete')) );
//var_export(mpnode( '*', mpnode('post', mpnode('edit'), mpnode('delete')), mpnode('forum', mpnode('edit'), mpnode('delete'))));
function insertMPTree( $db, $node, $parent = null ) {
  if( !$node ) 
    return;
  $db->exec( "INSERT INTO permtree (prgt, plft, parent, node) VALUES ('${node['rgtn']}', '${node['lftn']}', '$parent', '${node['val']}');" );
  insertMPTree( $db, $node['lft'], $node['val'] );
  insertMPTree( $db, $node['rgt'], $node['val'] );
}

try {
  $db->beginTransaction();


  /**
   * forum  ::
   *   id, title, datum, rank
   **/
  $db->exec( 'DROP TABLE IF EXISTS forum;' );
  $db->exec( 'CREATE TABLE forum 
                (id INTEGER PRIMARY KEY, 
                 title Text(200) NOT NULL, 
                 datum Text(200) NOT NULL, 
                 rank Integer NOT NULL);' );
  /**
   * thread ::
   *   id, fid, pid, prio, bumpdate, bumpuid
   **/
  $db->exec( 'DROP TABLE IF EXISTS thread;' );
  $db->exec( 'CREATE TABLE thread 
                (id INTEGER PRIMARY KEY, 
                 fid Integer NOT NULL,
                 pid Integer NOT NULL,
                 prio Integer NOT NULL,
                 bumpdate Integer NOT NULL,
                 bumpuid Integer NOT NULL);' );
  /**
   * post   ::
   *   id, fid(NULL), tid, uid, title, rank, indent, date, smurfkey
   **/
  $db->exec( 'DROP TABLE IF EXISTS post;' );
  $db->exec( 'CREATE TABLE post 
                (id INTEGER PRIMARY KEY, 
                 fid INTEGER NOT NULL, 
                 title Text(200) NOT NULL, 
                 rank Integer NOT NULL,
                 indent Integer NOT NULL, 
                 datum CLOB NOT NULL,
                 date Integer NOT NULL, 
                 uid Integer NOT NULL,
                 smurfKey Text(500) NOT NULL);' );

  /**
   * user :: 
   *   name, pass, email, avatar, joindate, grpid, timezone, botcheck, rank, karma
   **/
  $db->exec( 'DROP TABLE IF EXISTS user;' );
  $db->exec( 'CREATE TABLE user 
                (id INTEGER PRIMARY KEY, 
                 name Text(200) UNIQUE NOT NULL, 
                 pass Text(250) NOT NULL, 
                 mail Text(200) UNIQUE NOT NULL,
                 joinDate Integer NOT NULL,
                 permGroup Integer,
                 avatar Text(250));');
  /**
   * permgroup ::
   *   id, name
   **/
  $db->exec( 'DROP TABLE IF EXISTS permgroup;' ); 
  $db->exec( 'CREATE TABLE permgroup 
                (id INTEGER PRIMARY KEY,
                 name Text(250) NOT NULL );' );
  /**
   * permlist ::
   *   grpid, plft, prgt
   **/
  $db->exec( 'DROP TABLE IF EXISTS permlist;' );
  $db->exec( 'CREATE TABLE permlist 
                (grpid Integer NOT NULL,
                 plft Integer NOT NULL,
                 prgt Integer NOT NULL );' );
  /**
   * permtree ::
   *   parent, node, lft, rgt
   **/
  $db->exec( 'DROP TABLE IF EXISTS permtree;' );
  $db->exec( 'CREATE TABLE permtree
                (parent Integer NOT NULL,
                 node Integer NOT NULL,
                 plft Integer NOT NULL,
                 prgt Integer NOT NULL );' );



  // // SAMPLE POPULATION // //

  $db->exec( 'INSERT INTO permgroup (id, name) VALUES (1,"admin");' );
  $db->exec( 'INSERT INTO permlist (grpid, plft, prgt) VALUES (1, 1, 14);' );
  

  $db->exec( 'INSERT INTO permgroup (id, name) VALUES (2, "user");' );
  $db->exec( 'INSERT INTO permlist (grpid, plft, prgt) VALUES (2, 3, 4);' );
  $db->exec( 'INSERT INTO permlist (grpid, plft, prgt) VALUES (2, 9, 10);' );


  $ttree = 
array (
  'val' => '*',
  'lftn' => 1,
  'rgtn' => 14,
  'lft' => 
  array (
    'val' => 'post',
    'lftn' => 2,
    'rgtn' => 7,
    'lft' => 
    array (
      'val' => 'edit',
      'lftn' => 3,
      'rgtn' => 4,
      'lft' => 
      array (
      ),
      'rgt' => 
      array (
      ),
    ),
    'rgt' => 
    array (
      'val' => 'delete',
      'lftn' => 5,
      'rgtn' => 6,
      'lft' => 
      array (
      ),
      'rgt' => 
      array (
      ),
    ),
  ),
  'rgt' => 
  array (
    'val' => 'forum',
    'lftn' => 8,
    'rgtn' => 13,
    'lft' => 
    array (
      'val' => 'edit',
      'lftn' => 9,
      'rgtn' => 10,
      'lft' => 
      array (
      ),
      'rgt' => 
      array (
      ),
    ),
    'rgt' => 
    array (
      'val' => 'delete',
      'lftn' => 11,
      'rgtn' => 12,
      'lft' => 
      array (
      ),
      'rgt' => 
      array (
      ),
    ),
  ),
);
  insertMPTree( $db, $ttree );



  $db->exec( 'INSERT INTO user (id, name, pass, mail, joinDate, permGroup, avatar) VALUES (0, "guest", "", "guest@no-reply.com", '.time().', 4, "dolphin.jpg");' );

  $db->exec( 'INSERT INTO forum (id,title, datum, rank) VALUES(1,"f1","lots of love",1)');
  $db->exec( 'INSERT INTO post (id,fid, uid, title, rank, indent, date, smurfkey, datum) VALUES (1,1, 0, "foo", 0, 0, 12, 34, "apple pie"); ' );
  $db->exec( 'INSERT INTO thread (fid, pid, prio, bumpdate, bumpuid) VALUES(1, 1, 0, 12, 0); ' );


  $db->commit();
} catch ( Exception $e ) {
  $db->rollBack();
  die( $e->getMessage() );
}

echo 'DB BUILT';

?>
